import {api,db,upload} from "./base/server.js";
import XLSX  from "xlsx";
import fs from 'fs/promises'
import { generateToken, md5, paramList } from "./util/tool.js";





api.post("/uploadExcel",upload.single('file'), async  (req, res) => {
    if(!req.file) return res.status(400).json({error:'未上传文件'})
    // const {table} = req.query

    try {

        // 1. 获取上传文件路径
        const filePath = req.file.path;

        // 2. 读取 Excel 文件
        const workbook = XLSX.readFile(filePath);

        // 3. 获取第一个工作表
        const firstSheetName = workbook.SheetNames[0];
        const worksheet = workbook.Sheets[firstSheetName];
        const table = firstSheetName === 'pur' ? 'tb_flow_pur_records' : 
        firstSheetName === 'sale' ? 'tb_flow_sales_records' :
        firstSheetName === 'lotstore' ? 'tb_flow_lotstore_records' : 
        firstSheetName === 'store' ? 'tb_flow_store_records' : null;

        if(!table){
            return res.status(400).json({ error: '请勿修改模板sheet名称' });
        }

        // 4. 转为 JSON
        const data = XLSX.utils.sheet_to_json(worksheet);

        const pk = table + '_pk.NEXTVAL'

        if(table === "tb_flow_sales_records")
        for(var item of data){
            item.销售日期 = 
           await db.table(table)
            .insert({
                PK: {raw: pk},
                BILLID:item.单据编号,
                SALE_DATE:{raw:`To_Date('${parseExcelDate(item.销售日期)}', 'yyyy-mm-dd')`},
                SELLER_CODE: item.销售方代码,
                SELLER_NAME: item.名称,
                BUYER_CODE: item.采购方代码,
                BUYER_NAME: item.单位名称,
                MANUFACTURER: item.生产厂家,
                PRODUCT_CODE: item.产品编码,
                PRODUCT_NAME: item.商品名称,
                PRODUCT_SPEC: item.商品规格,
                PRODUCT_FORM: item.产品剂型,
                PRODUCT_GENERIC_NAME: item.产品通用名,
                BATCH_NO: item.批号,
                EXPIRY_DATE: {raw:`To_Date('${parseExcelDate(item.有效期至)}', 'yyyy-mm-dd')`},
                QUANTITY: item.数量,
                UNIT: item.单位,
                TAXED_PRICE: item.含税单价,
                TAXED_AMOUNT: item.含税金额,
                DELIVERY_ADDRESS: item.收货地址,
                IS_RETURN: item.是否退货,
                CREATE_DATE: {raw:`To_Date('${parseExcelDate(item.创建日期)}', 'yyyy-mm-dd')`},
            });
        }


        if(table === "tb_flow_pur_records")
        for(var item of data){
           await db.table(table)
            .insert({  
                PK: {raw: pk},
                SUMMARY : item.摘要,
                BILLINGdate : {raw:`To_Date('${parseExcelDate(item.日期)}', 'yyyy-mm-dd')`},
                BILLID : item.单据编号,
                CUSTNAME : item.单位名称,
                PRODUCT_CODE : item.商品编号,
                PRODUCT_NAME : item.商品名称,
                PRODUCT_SPEC : item.商品规格,
                MANUFACTURER : item.生产厂家,
                UNIT : item.单位,
                QUANTITY : item.数量,
                price : item.含税价,
                amount : item.含税金额,
                lotno : item.批号,
                PRODUCEdate : {raw:`To_Date('${parseExcelDate(item.生产日期)}', 'yyyy-mm-dd')`},
                EXPIRYdate : {raw:`To_Date('${parseExcelDate(item.有效期至)}', 'yyyy-mm-dd')`},
                CREATEdate : {raw:`sysdate`}
             });
        }

        if(table === "tb_flow_store_records")
        for(var item of data){
           await db.table(table)
            .insert({  
                PK: {raw: pk},
             });
        }

        if(table === "tb_flow_lotstore_records")
        for(var item of data){
           await db.table(table)
            .insert({  
                PK: {raw: pk},
             });
        }


        // 5. 返回解析结果
        res.json({
            success: true,
            msg: '文件上传并解析成功',
        });

    } catch (error) {
        console.error('解析失败:', error);
        res.status(500).json({ error: error.toString() });
    }finally{
        if (req.file.path) {
            try {
               await  fs.unlink(req.file.path);
            } catch (e) {
                console.error('删除临时文件失败:', e);
            }
        }
    }
})



api.get("/getSaleRecord", async (req, res) => {
    const {startDate,endDate,manufacturer,productCode,productName,userId} = req.query;
    const dataList = await db.table('tb_flow_sales_records')
    .select()
    .whereRaw(`
        (trunc(SALE_DATE) >=  to_date(:startDate,'yyyy-mm-dd')  or  :startDate is null)
        and (trunc(SALE_DATE) <= to_date(:endDate,'yyyy-mm-dd') or :endDate is null)
        and (MANUFACTURER like '%' || :manufacturer || '%'  or  :manufacturer is null)
        and (PRODUCT_CODE like '%' || :productCode || '%' or  :productCode is null)
        and (PRODUCT_NAME like '%' || :productName || '%' or  :productName is null)
        and (PRODUCT_CODE in (select PRODNO from tb_flow_sys_custofprod where USERID = :userId ) or :userId = 'admin')
        `, { startDate, endDate,manufacturer,productCode,productName,userId })
    .orderBy('SALE_DATE')
    .get();

    res.json({ success: true, data: dataList });
})


api.get("/getPurRecord", async (req, res) => {
    const {startDate,endDate,manufacturer,productCode,productName,userId} = req.query;
    const dataList = await db.table('tb_flow_pur_records')
    .select()
    .whereRaw(`
        (trunc(billingdate) >=  to_date(:startDate,'yyyy-mm-dd')  or  :startDate is null)
        and (trunc(billingdate) <= to_date(:endDate,'yyyy-mm-dd') or :endDate is null)
        and (MANUFACTURER like '%' || :manufacturer || '%'  or  :manufacturer is null)
        and (PRODUCT_CODE like '%' || :productCode || '%' or  :productCode is null)
        and (PRODUCT_NAME like '%' || :productName || '%' or  :productName is null)
        and (PRODUCT_CODE in (select PRODNO from tb_flow_sys_custofprod where USERID = :userId and deleteflag = 0 )  or :userId = 'admin')

        `, { startDate, endDate,manufacturer,productCode,productName,userId })
    .orderBy('billingdate')
    .get();

    res.json({ success: true, data: dataList });
})


api.post("/deleteRecord",async (req,res)=>{
    const {table,pkList} = req.body

    if (!Array.isArray(pkList) || pkList.length === 0) {
        return res.status(400).json({ success: false, msg: 'pk不能为空' });
    }

    try {
        const placeholders = pkList.map((_, i) => `:pk${i}`).join(',');
        const params = {};
        pkList.forEach((pk, i) => {
            params[`pk${i}`] = pk;
        });

        const sql = `delete ${table} where pk in (${placeholders}) `
        const result = await db.execute(sql,params)

        res.json({
        success:true,
        msg:'删除成功',
        data: result
    })

    } catch (error) {
        res.status(500).json({ success: false, msg: error.toString() });
    }
})















api.get("/user",async (req,res)=>{
    const params = req.query
    try {
        const dataList = await db.table('tb_flow_sys_userinfo')
            .select()
            .whereRaw(`(isactive = :ISACTIVE OR :ISACTIVE IS NULL) AND (USERID LIKE '%' || :USERID || '%' and CUSTOMER_NAME LIKE '%' || :CUSTOMER_NAME || '%')`,
                {USERID:params.USERID,CUSTOMER_NAME:params.CUSTOMER_NAME,ISACTIVE:params.ISACTIVE})
            .orderBy('PK','DESC')
            .get();
            res.json({ success:true, msg:'ok',data:dataList })
    } catch (error) {
        res.status(500).json({ success: false, msg: error.toString() });
    }
})

api.post("/user/resetPwd", async (req,res)=>{
    const user = req.body
    if (!user || Object.keys(user).length === 0) {
        return res.status(400).json({
            success: false,
            msg: '用户数据不能为空'
        });
    }

    try {
        const data = {
            PASSWORD:  md5(md5('123456')) ,
            UPDATETIME: new Date()
        }

        await db.table('tb_flow_sys_userinfo')
        .where({pk:user.PK})
        .update(data);

      res.json({
            success:true,
            msg:'密码重置成功',
            data:data
        })

    } catch (error) {
        res.status(500).json({ success: false, msg: error.toString() });
    }
})

api.post("/user/changePwd", async (req,res)=>{
    const {PK,oldPassword,newPassword} = req.body
    try {
        const user = await db.table('tb_flow_sys_userinfo')
        .where({pk:PK,PASSWORD:md5(md5(oldPassword)) })
        .get()

        if(user.length === 0){
           return res.json({ success:false, msg:'原密码错误' })
        }

        const data = {
            PASSWORD:  md5(md5(newPassword)) ,
            UPDATETIME: new Date()
        }
        await db.table('tb_flow_sys_userinfo')
        .where({pk:PK})
        .update(data);
      res.json({ success:true, msg:'密码修改成功', data:data })

    } catch (error) {
        res.status(500).json({ success: false, msg: error.toString() });
    }
})

api.post("/user/add",async (req,res)=>{
    const user = req.body
    if (!user || Object.keys(user).length === 0) {
        return res.status(400).json({
            success: false,
            msg: '用户数据不能为空'
        });
    }
    try {
        await db.table('tb_flow_sys_userinfo')
        .insert({
            pk: {raw:'tb_flow_sys_userinfo_pk.nextval'},
            USERID: user.USERID,
            PHONE: user.PHONE,
            CUSTOMER_NAME: user.CUSTOMER_NAME ,
            CUSTOMER_TYPE: user.CUSTOMER_TYPE ,
            CHARGE_AMOUNT: user.CHARGE_AMOUNT ,
            VALID_UNTIL: user.VALID_UNTIL ? new Date(user.VALID_UNTIL) :null ,
            REMARKS: user.REMARK,
            ISACTIVE: user.ISACTIVE,
            UPDATETIME: new Date(),
            CREATED_DATE: new Date(),
            PASSWORD: md5(md5('123456'))
        });

      res.json({
            success:true,
            msg:'添加成功',
        })

    } catch (error) {
        res.status(500).json({ success: false, msg: error.toString() });
    }
})

api.post("/user/updRole",async (req,res)=>{
    const {user} = req.body
    if (!user || Object.keys(user).length === 0) {
        return res.status(400).json({
            success: false,
            msg: '用户数据不能为空'
        });
    }
    console.log(user)
    try {
        await db.table('tb_flow_sys_userinfo')
        .where({PK:user.PK})
        .update({
            ROLES:user.ROLES
        });

      res.json({
            success:true,
            msg:'角色分配成功',
        })

    } catch (error) {
        res.status(500).json({ success: false, msg: error.toString() });
    }
})

api.post("/user/update",async (req,res)=>{
    const user = req.body
    if (!user || Object.keys(user).length === 0) {
        return res.status(400).json({
            success: false,
            msg: '用户数据不能为空'
        });
    }

    try {
        const data = {
            USERID: user.USERID ,
            PHONE: user.PHONE ,
            CUSTOMER_NAME: user.CUSTOMER_NAME ,
            CUSTOMER_TYPE: user.CUSTOMER_TYPE ,
            CHARGE_AMOUNT: user.CHARGE_AMOUNT ,
            VALID_UNTIL: user.VALID_UNTIL ? new Date(user.VALID_UNTIL) : null,
            REMARKS: user.REMARKS ,
            ISACTIVE: user.ISACTIVE ,
            UPDATETIME: new Date()
        }

        await db.table('tb_flow_sys_userinfo')
        .where({pk:user.PK})
        .update(data);

      res.json({
            success:true,
            msg:'修改成功',
            data:data
        })

    } catch (error) {
        res.status(500).json({ success: false, msg: error.toString() });
    }
})


api.post("/user/del",async (req,res)=>{
    const {pkList} = req.body

    if (!Array.isArray(pkList) || pkList.length === 0) {
        return res.status(400).json({ success: false, msg: 'pk不能为空' });
    }

    const placeholders = pkList.map((_, i) => `:pk${i}`).join(',');
        const params = {};
        pkList.forEach((pk, i) => {
            params[`pk${i}`] = pk;
        });

    const sql = `delete tb_flow_sys_userinfo where pk in (${placeholders}) `
    await db.execute(sql,params)

        res.json({
            success:true,
            msg:'删除成功',
        })
})



api.get("/roles",async (req,res)=>{
    // const params = req.query
    // try {
        const dataList = await db.table('tb_flow_sys_role')
            .select()
            .orderBy('PK')
            .get();
        res.json({ success:true, msg:'ok',data:dataList })
    // } catch (error) {
    //     res.status(500).json({ success: false, msg: error.toString() });
    // }
})

api.post("/roles/add",async (req,res)=>{
    const {role} = req.body
    if (!role || Object.keys(role).length === 0) {
        return res.status(400).json({
            success: false,
            msg: '角色数据不能为空'
        });
    }
    await db.table('tb_flow_sys_role')
            .insert({
                pk: {raw:'tb_flow_sys_role_pk.nextval'},
                ROLENAME:role.ROLENAME,
                DELETEFLAG:0,
                CREATETIME:new Date()
            })

        res.json({ success:true, msg:'添加成功'})
})

api.post("/roles/del",async (req,res)=>{
    const {role} = req.body
    if (!role || Object.keys(role).length === 0) {
        return res.status(400).json({
            success: false,
            msg: '角色数据不能为空'
        });
    }
    await db.table('tb_flow_sys_role')
            .where({pk:role.PK})
            .delete()

        res.json({ success:true, msg:'删除成功'})
})


api.post("/roles/menus",async (req,res)=>{
    const {role} = req.body
    if (!role || Object.keys(role).length === 0) {
        return res.status(400).json({
            success: false,
            msg: '角色数据不能为空'
        });
    }
    await db.table('tb_flow_sys_role')
            .where({pk:role.PK})
            .update({
                MENUS:role.MENUS
            })

        res.json({ success:true, msg:'菜单分配成功'})
})


api.get("/menu",async (req,res)=>{
    const params = req.query
    try {
        const dataList = await db.table('tb_flow_sys_menu')
            .select()
            .orderBy('PK')
            .get();
        res.json({ success:true, msg:'ok',data:dataList })
    } catch (error) {
        res.status(500).json({ success: false, msg: error.toString() });
    }
})

api.post("/getMenuByRole",async (req,res)=>{
    const {pkList} = req.body
     const placeholders = pkList.map((_, i) => `:pk${i}`).join(',');
        const params = {};
        pkList.forEach((pk, i) => {
            params[`pk${i}`] = pk;
        });

    try {
    const sql = `SELECT * FROM tb_flow_sys_menu 
                WHERE pk IN (
                    SELECT TO_NUMBER(TRIM(REGEXP_SUBSTR(MENUS, '[^,]+', 1, LEVEL))) 
                    FROM (
                        SELECT MENUS FROM tb_flow_sys_role WHERE pk in (${placeholders})
                    )
                    CONNECT BY LEVEL <= LENGTH(REGEXP_REPLACE(MENUS, '[^,]+', '')) + 1
                )`
        const dataList = await db.execute(sql,params)
        
        res.json({ success:true, msg:'ok',data:dataList.rows })
    } catch (error) {
        res.status(500).json({ success: false, msg: error.toString() });
    }
})



api.post("/login", async (req, res) => {
    const { userId, password } = req.body;
    
    try {
        const user = await db.table('tb_flow_sys_userinfo')
            .select()
            .where({ 
                USERID: userId,
                PASSWORD: md5(md5(password))
            })
            .first();
            
        if (!user) {
            return res.json({
                success: false,
                msg: '用户名或密码错误'
            });
        }
        
        // 生成 token
        const token = generateToken(user);
        
        res.json({
            success: true,
            msg: '登录成功',
            data: {
                token,
                user: user
            }
        });
    } catch (error) {
        res.status(500).json({ 
            success: false, 
            msg: error.toString() 
        });
    }
});


api.get("/custProd", async (req, res) => {
    const {CUSTOMER_NAME,USERID , PRODNO } = req.query;

    const sql = `select a.*,b.customer_name
    from tb_flow_sys_custofprod a
    left join tb_flow_sys_userinfo b
    on a.userid = b.userid
    where (a.userId LIKE '%'||:USERID||'%' )
    and (a.prodno LIKE '%'||:PRODNO||'%') `

    // try {
        const dataList = await db.execute(sql,{USERID:USERID,PRODNO:PRODNO})
        res.json({ success: true, msg: 'ok',data:dataList.rows }); 
    // } catch (error) {
    //     res.status(500).json({ success: false, msg: error.toString() });
    // }

    

    
});


api.post("/custProd/add", async (req, res) => {
    const { CUSTOMER_NAME, USERID,PRODNO } = req.body;

    await db.table('tb_flow_sys_custofprod')
            .insert({
                pk: {raw:'tb_flow_sys_custofprod_pk.nextval'},
                deleteflag:0,
                createtime:new Date(),
                userid:USERID,
                PRODNO:PRODNO
            });

    res.json({ success: true, msg: '添加成功' }); 
});


api.post("/custProd/del", async (req, res) => {
    const { pkList } = req.body;
    const {placeholders,params} = paramList(pkList)

    const sql = `delete from  tb_flow_sys_custofprod where pk in (${placeholders})  `;
    await db.execute(sql,params)

    res.json({ success: true, msg: '删除成功' }); 
});



api.get('/downloadTemplate', async (req,res) => {
  const { table } = req.query;

  try {
    // 从模板目录读取文件
    const templatePath = `./excelTemplate/${table}.xlsx`;
    const fileBuffer = await fs.readFile(templatePath);
    
    // res.setHeader('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    // res.setHeader('Content-Disposition', `attachment; filename=${Buffer.from(table + '模板.xlsx').toString('latin1')}`);
    
    // 发送文件
    res.send(fileBuffer);
    
  } catch (error) {
    console.error('下载模板失败:', error);
    res.status(500).json({ 
      success: false, 
      msg: '下载模板失败: ' + error.message 
    });
  }
});




